# -*- coding: utf-8 -*-
from tkinter import *
import tkinter.font as tkFont
from tkinter.ttk import Treeview
from tkinter import scrolledtext
import pymysql
import numpy as np
import openpyxl
import win32ui
import matplotlib.pyplot as plt
import tkinter.messagebox as messagebox

global user_r;
global password_r
user_r = "root";
password_r = "123456"


# 数据库根连接
def root_conn(sql):
    user = user_r;
    password = password_r
    conn = pymysql.connect(host="localhost", user=user, password=password, database='tank', charset="utf8")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(sql)
    ret = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return ret


# 用户访问数据库
def sql_conn(sql, user, password):
    conn = pymysql.connect(host="localhost", user=user, password=password, database="tank", charset="utf8")
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(sql)
    ret = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return ret


def index(window, user, password):
    name = str(user)  # 当前使用的表名默认为用户名
    usr = str(user)
    psw = str(password)  # 以用户身份访问数据库
    orders = {'升序': 'asc', '降序': 'desc'}
    order = '降序'

    def initMenu(window_index):
        '''初始化菜单'''
        mbar = Menu(window_index)  # 定义顶级菜单实例
        fmenu = Menu(mbar, tearoff=False)  # 在顶级菜单下创建菜单项
        sort_menu = Menu(mbar, tearoff=False)
        subsort_menu = Menu(sort_menu, tearoff=False)
        stat_menu = Menu(mbar, tearoff=False)
        chart_menu = Menu(mbar, tearoff=False)
        copp_menu = Menu(mbar, tearoff=False)

        # 添加子菜单:文件
        mbar.add_cascade(label="文件", menu=fmenu, font=('Times', 20, 'bold'))
        fmenu.add_command(label="查看本地表格", command=lambda: local_table())
        fmenu.add_command(label="新建空白表格", command=lambda: new_table(name, usr, psw))
        fmenu.add_command(label="导出当前表格", command=lambda: save_table(name, usr, psw))
        fmenu.add_command(label="导入本地表格", command=lambda: import_table(name, usr, psw))
        fmenu.add_separator()  # 添加分割线
        fmenu.add_command(label="退出程序", command=usr_quit)
        # 添加子菜单:排序
        mbar.add_cascade(label="排序", menu=sort_menu, font=('Times', 20, 'bold'))
        sort_menu.add_command(label="按姓名排序", command=lambda: sort_by_name(name, usr, psw))
        sort_menu.add_command(label="按学号排序", command=lambda: sort_by_id(name, usr, psw))
        sort_menu.add_separator()
        sort_menu.add_cascade(label="按成绩排序", menu=subsort_menu, font=('Times', 10))
        subsort_menu.add_command(label="按3000米成绩排序", command=lambda: sort_by_grade_l(name, usr, psw))
        subsort_menu.add_command(label="按30*2蛇形跑成绩排序", command=lambda: sort_by_grade_m(name, usr, psw))
        subsort_menu.add_command(label="按引体向上成绩排序", command=lambda: sort_by_grade_e(name, usr, psw))
        subsort_menu.add_separator()
        subsort_menu.add_command(label="按仰卧起坐成绩排序", command=lambda: sort_by_grade_p(name, usr, psw))
        subsort_menu.add_command(label="按总分排序", command=lambda: sort_by_grade_c(name, usr, psw))
        # 添加子菜单:统计工具
        mbar.add_cascade(label="统计工具", menu=stat_menu, font=('Times', 20, 'bold'))
        stat_menu.add_command(label="统计学生总数", command=lambda: stat_num(name, usr, psw))
        stat_menu.add_command(label="统计平均成绩", command=lambda: stat_aver(name, usr, psw))
        stat_menu.add_command(label="统计成绩标准差", command=lambda: stat_std(name, usr, psw))
        stat_menu.add_command(label="统计成绩中位数", command=lambda: stat_mid(name, usr, psw))
        # 添加子菜单:可视化
        mbar.add_cascade(label="可视化", menu=chart_menu, font=('Times', 20, 'bold'))
        chart_menu.add_command(label="学生成绩柱形图", command=lambda: barchart(name, usr, psw))
        chart_menu.add_command(label="学生成绩饼状图", command=lambda: piechart(name, usr, psw))
        # 添加子菜单:本地协作
        mbar.add_cascade(label="本地协作", menu=copp_menu, font=('Times', 20, 'bold'))
        copp_menu.add_command(label="查看本地用户名", command=local_user)
        copp_menu.add_command(label="数据库权限赋予", command=lambda: grant_user(name, usr, psw))
        # 添加子菜单:帮助
        mbar.add_command(label='帮助', command=usr_help)
        # 将顶级菜单注册到窗体
        window_index.config(menu=mbar)

    # 文件模块
    # 退出的函数
    def usr_quit():
        window.destroy()

    def local_table():
        local_table_window = Toplevel(window_index)
        local_table_window.geometry('350x300')
        local_table_window.title('本地协作')
        sda = scrolledtext.ScrolledText(local_table_window, width=24, height=12, font=('宋体', 14))
        sda.place(x=30, y=30)
        sql = 'show tables;'
        ret = root_conn(sql)
        user_list = '本地表格列表：\n'
        for i in range(len(ret)):
            user_list += str(i + 1)
            user_list += '. '
            user_list += str(ret[i]['Tables_in_tank'])
            user_list += '\n'
        sda.insert(END, user_list)

    def new_table(name, usr, psw):
        def new_table_new(usr, psw):
            nn = new_name.get()
            # GRANT CREAT ON tank TO '"+nn+"'@'localhost';
            sql1 = "CREATE TABLE " + nn + "(" + "name varchar(255) DEFAULT NULL," + "id varchar(11) NOT NULL," + \
                   "grade_l varchar(25) DEFAULT NULL," + "grade_m varchar(25) DEFAULT NULL," + "grade_e varchar(25) DEFAULT NULL," + \
                   "grade_p varchar(25) DEFAULT NULL," + "grade_c varchar(25) DEFAULT NULL," + "grade_b varchar(25) DEFAULT NULL," + \
                   "PRIMARY KEY (id))" + "ENGINE=InnoDB DEFAULT CHARSET=utf8;"
            sql2 = "GRANT ALL ON tank." + nn + " TO '" + usr + "'@'localhost' with grant option;"
            sql3 = "FLUSH PRIVILEGES;"
            try:
                ret = root_conn(sql1)
                ret2 = root_conn(sql2)
                ret3 = root_conn(sql3)
                messagebox.showinfo('恭喜', '表格创建成功')
            except:
                try:
                    sql4 = "grant create on tank to '" + usr + "'@'localhost';"
                    ret4 = root_conn(sql4)
                    ret = root_conn(sql1);
                    ret2 = root_conn(sql2);
                    ret3 = root_conn(sql3)
                    messagebox.showinfo('恭喜', '表格创建成功')
                except:
                    print(sql1);
                    print(sql2)
                    messagebox.showerror('警告', '要创建的表格已存在！')

        # 新建表格界面
        window_new_table = Toplevel(window_index)
        window_new_table.geometry('350x200')
        window_new_table.title('新建本地表格')
        # 表格名变量及标签、输入框、按钮
        new_name = StringVar()
        Label(window_new_table, text='表格名： ').place(x=30, y=30)
        Entry_new_table = Entry(window_new_table, textvariable=new_name, highlightcolor='blue', highlightthickness=1)
        Entry_new_table.place(x=120, y=30)
        bt_new_table = Button(window_new_table, text='新建表格', command=lambda: new_table_new(usr, psw), height=1,
                              width=20)
        bt_new_table.place(x=100, y=80)

    def save_table(name, usr, psw):
        filename_filter = "Excel Workbook(*.xlsx)|*.xlsx||"
        dlg = win32ui.CreateFileDialog(0, None, None, 1, filename_filter, None)
        dlg.SetOFNInitialDir("C:")  #
        flag = dlg.DoModal()
        if 1 == flag:
            save_path = dlg.GetPathName()
            sql = "select * from " + name + " order by id " + orders[order] + ";"
            ret = sql_conn(sql, usr, password=psw)
            wb = openpyxl.Workbook()
            ws = wb.create_sheet(index=0, title="savedsheet")
            ws.append(['姓名', '学号', '3000米', '30*2蛇形跑', '引体向上', '仰卧起坐', '总分', '总评'])
            for i in range(len(ret)):
                ws.append([ret[i]['name'], int(ret[i]['id']), float(ret[i]['grade_l']), float(ret[i]['grade_m']), \
                           float(ret[i]['grade_e']), float(ret[i]['grade_p']), float(ret[i]['grade_c']),
                           float(ret[i]['grade_b'])])
            wb.save(save_path)
            messagebox.showinfo('系统信息', '保存成功!')
        else:
            is_saved = messagebox.askyesno('导出已取消', '还没有导出文件，是否重新导出？')
            if is_saved:
                save_table(name, usr, psw)

    def import_table(name, usr, psw):
        filename_filter = "Excel Workbook(*.xlsx)|*.xlsx||"
        dlg = win32ui.CreateFileDialog(1, None, None, 1, filename_filter, None)
        dlg.SetOFNInitialDir(r'C:\Users\Administrator\Desktop')
        dlg.DoModal()
        import_path = dlg.GetPathName()  # 获取选择的文件名称
        wb = openpyxl.load_workbook(import_path)
        ws = wb.active
        uncounted = []
        for i in range(2, ws.max_row):
            rowtmp = []
            for j in range(1, ws.max_column + 2):
                celltmp = ws.cell(row=i, column=j).value
                rowtmp.append(str(celltmp))
            sql = "insert into " + name + " values ('" + rowtmp[0] + "','" + rowtmp[1] + "','" + rowtmp[2] + "','" + \
                  rowtmp[3] + "','" + rowtmp[4] + "','" + rowtmp[5] + "','" + rowtmp[6] + "','" + rowtmp[7] + "');"
            try:
                ret = sql_conn(sql, usr, password=psw)
            except:
                uncounted.append(str(rowtmp[0]))
                continue
        delete_tab(table)
        all_data(name, usr, psw)
        if len(uncounted) == 0:
            messagebox.showinfo('系统信息', '数据导入成功')
        else:
            messagebox.showinfo('下列学生因为学号重复导致导入失败：', uncounted)

    # 统计模块
    # 统计学生总数
    def stat_num(name, usr, psw):
        if names.get() == '' and ids.get() == '':
            sql = "select count(id) from " + name + ";"
        else:
            sql = "select count(id) from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "';"
        ret = sql_conn(sql, usr, password=psw)
        messagebox.showinfo('学生总数：', ret[0]['count(id)'])

    # 统计学生成绩平均数
    def stat_aver(name, usr, psw):
        avers = []
        grades = [grade_l, grade_m, grade_e, grade_p, grade_c]
        grades_n = ['grade_l', 'grade_m', 'grade_e', 'grade_p', 'grade_c']
        subjects = ['3000米', '30*2蛇形跑', '引体向上', '仰卧起坐', '总分']
        for i in range(len(grades)):
            if names.get() == '' and ids.get() == '' and grades[i].get() == '':
                sql1 = "select sum(" + grades_n[i] + ") from " + name + ";"
                sql2 = "select count(id) from " + name + ";"
            else:
                sql1 = "select sum(" + grades_n[
                    i] + ") from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "' or " + \
                       grades_n[i] + "='" + grades[i].get() + "';"
                sql2 = "select count(id) from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "' or " + \
                       grades_n[i] + "='" + grades[i].get() + "';"
            ret1 = sql_conn(sql1, usr, password=psw)
            ret2 = sql_conn(sql2, usr, password=psw)
            aver = ret1[0]['sum(' + grades_n[i] + ')'] / ret2[0]['count(id)']
            avers.append(aver)
        result = ''
        for i in range(len(avers)):
            result += subjects[i]
            result += '成绩平均值：'
            result += str(avers[i])
            result += '\n'
        messagebox.showinfo('学生成绩平均数：', result)

    # 统计学生成绩标准差
    def stat_std(name, usr, psw):
        stds = []
        grades = [grade_l, grade_m, grade_e, grade_p, grade_c]
        grades_n = ['grade_l', 'grade_m', 'grade_e', 'grade_p', 'grade_c']
        subjects = ['3000米', '30*2蛇形跑', '引体向上', '仰卧起坐', '总分']
        for i in range(len(grades)):
            if names.get() == '' and ids.get() == '' and grades[i].get() == '':
                sql1 = "select " + grades_n[i] + " from " + name + ";"
            else:
                sql1 = "select " + grades_n[
                    i] + " from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "' or " + grades_n[
                           i] + "='" + grades[i].get() + "';"
            ret1 = sql_conn(sql1, usr, password=psw)
            ret2 = []
            for j in range(len(ret1)):
                # print(int(ret1[j][grades_n[i]]))
                try:
                    ret2.append(int(ret1[j][grades_n[i]]))
                except:
                    continue
            ret2 = np.array(ret2)
            std = ret2.std()
            stds.append(std)
        result = ''
        for i in range(len(stds)):
            result += subjects[i]
            result += '成绩标准差：'
            result += str(stds[i])
            result += '\n'
        messagebox.showinfo('学生成绩标准差：', result)

    # 统计学生成绩中位数
    def stat_mid(name, usr, psw):
        mids = []
        grades = [grade_l, grade_m, grade_e, grade_p, grade_c]
        grades_n = ['grade_l', 'grade_m', 'grade_e', 'grade_p', 'grade_c']
        subjects = ['3000米', '30*2蛇形跑', '引体向上', '仰卧起坐', '总分']
        for i in range(len(grades)):
            if names.get() == '' and ids.get() == '':
                sql1 = "select " + grades_n[i] + " from " + name + " order by " + grades_n[i] + ";"
            else:
                sql1 = "select " + grades_n[
                    i] + " from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "';"
            ret1 = sql_conn(sql1, usr, password=psw)
            j = int(len(ret1) / 2)
            mid = ret1[j][grades_n[i]]
            mids.append(mid)
        result = ''
        for i in range(len(mids)):
            result += subjects[i]
            result += '成绩中位数：'
            result += str(mids[i])
            result += '\n'
        messagebox.showinfo('学生成绩中位数：', result)

    # 排序模块
    def sort_by_name(name, usr, psw):
        sql = "select * from " + name + " order by name " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_id(name, usr, psw):
        sql = "select * from " + name + " order by id " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_grade_l(name, usr, psw):
        sql = "select * from " + name + " order by grade_l " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_grade_m(name, usr, psw):
        sql = "select * from " + name + " order by grade_m " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_grade_e(name, usr, psw):
        sql = "select * from " + name + " order by grade_e " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_grade_p(name, usr, psw):
        sql = "select * from " + name + " order by grade_p " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    def sort_by_grade_c(name, usr, psw):
        sql = "select * from " + name + " order by grade_c " + orders[v.get()] + ";"
        ret = sql_conn(sql, usr, password=psw)
        delete_tab(table)
        tab(ret)

    # 可视化模块
    def survey(results, category_names):
        plt.rcParams['font.sans-serif'] = ['SimHei']
        labels = list(results.keys())
        data = np.array(list(results.values()))
        data_cum = data.cumsum(axis=1)
        category_colors = plt.get_cmap('RdYlGn')(
            np.linspace(0.15, 0.85, data.shape[1]))
        fig, ax = plt.subplots(figsize=(9.2, 5))
        ax.invert_yaxis()
        ax.xaxis.set_visible(False)
        ax.set_xlim(0, np.sum(data, axis=1).max())
        for i, (colname, color) in enumerate(zip(category_names, category_colors)):
            widths = data[:, i]
            starts = data_cum[:, i] - widths
            ax.barh(labels, widths, left=starts, height=0.5,
                    label=colname, color=color)
            xcenters = starts + widths / 2
            r, g, b, _ = color
            text_color = 'white' if r * g * b < 0.5 else 'darkgrey'
            for y, (x, c) in enumerate(zip(xcenters, widths)):
                ax.text(x, y, str(int(c)), ha='center', va='center',
                        color=text_color)
        ax.legend(ncol=len(category_names), bbox_to_anchor=(0, 1),
                  loc='lower left', fontsize='small')
        return fig, ax

    def barchart(name, usr, psw):
        category_names = ['优秀', '良好', '及格', '不及格']
        results = {'3000米': [3, 2, 8, 2],
                   '30*2蛇形跑': [1, 3, 7, 4],
                   '引体向上': [1, 4, 7, 3],
                   '仰卧起坐': [1, 3, 10, 1],
                   '总分': [1, 1, 7,6]}
        survey(results, category_names)
        plt.show()

    def piechart(name, usr, psw):
        category_names = ['优秀', '良好', '及格', '不及格']
        results = {'3000米': [3, 2, 8, 2],
                   '30*2蛇形跑': [1, 3, 7, 4],
                   '引体向上': [1, 4, 7, 3],
                   '仰卧起坐': [1, 3, 10, 1],
                   '总分': [1, 1, 7,6]}
        plt.rcParams['font.sans-serif'] = ['SimHei']
        fig = plt.figure(figsize=(15, 7))
        labels = list(category_names)
        data = np.array(list(results.values()))
        explode = (0.1, 0, 0, 0)  # 将第一块分离出来
        for i in range(len(data)):
            ax = fig.add_subplot(2, 3, i + 1)
            current_key = list(results.keys())[i]
            current_data = np.array(results[current_key])

            def func(pct, allvals):
                absolute = int(pct / 100. * np.sum(allvals))
                return "{:.1f}%\n({:d} 人)".format(pct, absolute)

            color = ['red', 'orange', 'yellow', 'yellowgreen', 'green']
            wedges, texts, autotexts = ax.pie(current_data, colors=color, explode=explode,
                                              autopct=lambda pct: func(pct, current_data),
                                              textprops=dict(fontsize=10, color='black'),
                                              labeldistance=1.8, startangle=90, pctdistance=1.4)
            title = current_key
            ax.legend(wedges, category_names, title=title, loc="center left", bbox_to_anchor=(1.1, 0, 0.5, 1.1))

        plt.show()

    # 本地协作功能
    def local_user():
        copp_window = Toplevel(window_index)
        copp_window.geometry('350x300')
        copp_window.title('本地协作')
        sda = scrolledtext.ScrolledText(copp_window, width=24, height=12, font=('宋体', 14))
        sda.place(x=30, y=30)
        sql = 'select User from mysql.user;'
        ret = root_conn(sql)
        user_list = '本地用户名单：\n'
        for i in range(len(ret)):
            user_list += str(i + 1)
            user_list += '. '
            user_list += str(ret[i]['User'])
            user_list += '\n'
        sda.insert(END, user_list)

    def grant_user(name, usr, psw):
        def grant(usr, psw):
            sql = "GRANT ALL ON tank." + target_table.get() + " TO '" + target_user.get() + "'@'localhost' with grant option;"
            sql2 = "FLUSH PRIVILEGES;"
            try:
                sql_conn(sql, usr, psw)
                messagebox.showinfo('恭喜', '权限赋予成功！')
            except:
                messagebox.showinfo('系统信息', '权限赋予失败，您是否拥有本表格所有权限？')
            root_conn(sql2)

        def limited_grant(usr, psw):
            sql = "GRANT ALL ON tank." + target_table.get() + " TO '" + target_user.get() + "'@'localhost';"
            sql2 = "FLUSH PRIVILEGES;"
            try:
                sql_conn(sql, usr, psw)
                messagebox.showinfo('恭喜', '权限赋予成功！')
            except:
                messagebox.showinfo('系统信息', '权限赋予失败，您是否拥有本表格权限？')
            root_conn(sql2)

        def readonly_grant(usr, psw):
            sql = "GRANT SELECT ON tank." + target_table.get() + " TO '" + target_user.get() + "'@'localhost';"
            sql2 = "FLUSH PRIVILEGES;"
            try:
                sql_conn(sql, usr, psw)
                messagebox.showinfo('恭喜', '权限赋予成功！')
            except:
                messagebox.showinfo('系统信息', '权限赋予失败，您是否拥有本表格权限？')
            root_conn(sql2)

        # 权限授予界面
        grant_window = Toplevel(window_index)
        grant_window.geometry('350x200')
        grant_window.title('权限授予界面')
        # 表格名变量及标签、输入框、按钮
        target_user = StringVar()
        target_table = StringVar()
        Label(grant_window, text='目标用户名：').place(x=30, y=30)
        Entry(grant_window, textvariable=target_user, highlightcolor='blue', highlightthickness=1).place(x=140, y=30)
        Label(grant_window, text='目标表格名：').place(x=30, y=60)
        Entry(grant_window, textvariable=target_table, highlightcolor='blue', highlightthickness=1).place(x=140, y=60)
        bt_new_table = Button(grant_window, text='完全权限赋予', command=lambda: grant(usr, psw), height=1, width=12)
        bt_new_table.place(x=5, y=120)
        bt_new_table = Button(grant_window, text='有限权限赋予', command=lambda: limited_grant(usr, psw), height=1, width=12)
        bt_new_table.place(x=100, y=120)
        bt_new_table = Button(grant_window, text='查看权限赋予', command=lambda: readonly_grant(usr, psw), height=1, width=12)
        bt_new_table.place(x=195, y=120)

    # 帮助文档模块
    def usr_help():
        help_window = Toplevel(window_index)
        help_window.geometry('680x650')
        help_window.title('帮助')
        sda = scrolledtext.ScrolledText(help_window, width=60, height=30, font=('宋体', 14))
        sda.place(x=30, y=30)
        try:
            f = open('help.txt', 'r', encoding='utf-8')
            s = f.read()
            sda.insert(END, s)
        except:
            messagebox.showerror('警告', '帮助文档已被删除，无法读取！')

    # 遍历全部数据
    def all_data(name, usr, psw):
        sql = "select * from " + name + " order by id " + orders[order]
        try:
            ret = sql_conn(sql, usr, password=psw)
            tab(ret)
        except:
            messagebox.showerror('警告', '您没有当前表格的访问权限！')

    # 遍历table数据
    def tab(ret):
        for i in range(len(ret)):
            table.insert('', i, values=(ret[i]['name'], ret[i]['id'], ret[i]['grade_l'],
                                        ret[i]['grade_m'], ret[i]['grade_e'], ret[i]['grade_p'], ret[i]['grade_c'],
                                        ret[i]['grade_b'],))

    # 清空table
    def delete_tab(table):
        items = table.get_children()
        [table.delete(item) for item in items]

    # 查询功能
    def look(name, usr, psw):
        delete_tab(table)
        is_grade_emp = grade_l.get() == '' and grade_m.get() == '' and grade_e.get() == '' \
                       and grade_p.get() == '' and grade_c.get() == '' and grade_b.get() == ''
        if names.get() == '' and ids.get() == '' and is_grade_emp:
            all_data(name, usr, psw)
        else:
            try:
                sql = "select * from " + name + " where name='" + names.get() + "' or id='" + ids.get() + \
                      "' or grade_l='" + grade_l.get() + "' or grade_m='" + grade_m.get() + "' or grade_e='" + grade_e.get() + \
                      "' or grade_p='" + grade_p.get() + "' or grade_c='" + grade_c.get() + "' or grade_b='" + grade_b.get() + "';"
                ret = sql_conn(sql, usr, password=psw)
                delete_tab(table)
                tab(ret)
            except:
                messagebox.showerror('警告', '您没有当前表格的访问权限！')

    # 修改功能，覆盖修改，但是不影响其他科目的成绩，可以一个一个地修改
    def alter(name, usr, psw):
        if ids.get() == '':
            messagebox.showerror('警告', '请填写完整(需要学号)！')
        else:
            try:
                if names.get() != '':
                    sql = "update " + name + " set name='" + names.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_l.get() != '':
                    sql = "update " + name + " set grade_l='" + grade_l.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_m.get() != '':
                    sql = "update " + name + " set grade_m='" + grade_m.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_e.get() != '':
                    sql = "update " + name + " set grade_e='" + grade_e.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_p.get() != '':
                    sql = "update " + name + " set grade_p='" + grade_p.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_b.get() != '':
                    sql = "update " + name + " set grade_b='" + grade_b.get() + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                ret = sql_conn(sql, usr, password=psw)
                delete_tab(table)
                all_data(name, usr, psw)
                messagebox.showinfo('', '修改成功!')
            except:
                messagebox.showerror('警告', '您没有当前表格的修改权限，您可以向有授权许可的用户申请！')

    # 插入功能
    def insert(name, usr, psw):
        if names.get() == '' and ids.get() == '' and grade_b.get() == '':
            messagebox.showerror('警告', '请填写完整！')
        else:
            sql0 = "select count(id) from " + name + " where id='" + ids.get() + "';"
            ret = sql_conn(sql0, usr, password=psw)
            if ret[0]['count(id)'] != 0:
                messagebox.showerror('警告', '插入字段已存在！')
            else:
                try:
                    sql = "insert into " + name + " values ('" + names.get() + "','" + ids.get() + "','" + grade_l.get() + "','" + \
                          grade_m.get() + "','" + grade_e.get() + "','" + grade_p.get() + "','" + grade_c.get() + "','" + grade_b.get() + "');"
                    ret = sql_conn(sql, usr, password=psw)
                    messagebox.showinfo('', '插入成功!')
                    delete_tab(table)
                    all_data(name, usr, psw)
                except:
                    messagebox.showerror('警告', '您没有当前表格的修改权限，您可以向有授权许可的用户申请！')

    # 单项数据删除功能，可以做到只删除某一项成绩而不影响其他科目的成绩
    def delete(name, usr, psw):
        is_grade_emp = grade_l.get() == '' and grade_m.get() == '' and grade_e.get() == '' \
                       and grade_p.get() == '' and grade_c.get() == '' and grade_b.get() == ''
        if ids.get() == '':
            messagebox.showerror('警告', '请填写完整！')
        elif ids.get() != '' and is_grade_emp:
            is_empty = messagebox.askyesno('警告', '这将导致该学生的成绩信息被清空，是否继续？')
            if is_empty:
                sql = "update " + name + " set grade_l='',grade_m='',grade_e='',grade_p='',grade_c='',grade_b=''" + \
                      " where id='" + ids.get() + "';"
                ret = sql_conn(sql, usr, password=psw)
                delete_tab(table)
                all_data(name, usr, psw)
        else:
            try:
                if names.get() != '':
                    sql = "update " + name + " set name='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_l.get() != '':
                    sql = "update " + name + " set grade_l='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_m.get() != '':
                    sql = "update " + name + " set grade_m='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_e.get() != '':
                    sql = "update " + name + " set grade_e='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_p.get() != '':
                    sql = "update " + name + " set grade_p='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_c.get() != '':
                    sql = "update " + name + " set grade_c='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                if grade_b.get() != '':
                    sql = "update " + name + " set grade_b='" + "' where id='" + ids.get() + "';"
                    ret = sql_conn(sql, usr, password=psw)
                delete_tab(table)
                messagebox.showinfo('', '单项删除成功!')
                delete_tab(table)
                all_data(name, usr, psw)
            except:
                messagebox.showerror('警告', '您没有当前表格的修改权限，您可以向有授权许可的用户申请！')

    # 批量数据删除功能，删除姓名或学号匹配的所有项
    def muti_delete(name, usr, psw):
        if names.get() == '' and ids.get() == '':
            is_deleteall = messagebox.askyesno('警告', '这将导致表中所有数据被删除，是否继续？')
            if is_deleteall:
                try:
                    sql = "delete from " + name + ";"
                    ret = sql_conn(sql, usr, password=psw)
                    messagebox.showinfo('系统信息', '数据成功清空!')
                    delete_tab(table)
                    all_data(name, usr, psw)
                except:
                    messagebox.showerror('警告', '您没有当前表格的修改权限，您可以向有授权许可的用户申请！')
        else:
            try:
                sql = "delete from " + name + " where name='" + names.get() + "' or id='" + ids.get() + "';"
                ret = sql_conn(sql, usr, password=psw)
                messagebox.showinfo('系统信息', '删除成功!')
                delete_tab(table)
                all_data(name, usr, psw)
            except:
                messagebox.showerror('警告', '您没有当前表格的修改权限，您可以向有授权许可的用户申请！')

    def table_jump_fun(self):
        table_jump()

    # 表格跳转功能
    def table_jump():
        nonlocal name
        name = to_table.get()
        try:
            delete_tab(table)
            all_data(name, usr, psw)
        except:
            messagebox.showerror('您没有当前表格的访问权限！', '您可以在文件菜单栏中查看本地表格')

    # 数据是顺序的还是倒序的
    def order_event():
        nonlocal order
        order = v.get()
        ck['text'] = order

    window_index = Toplevel(window)
    initMenu(window_index)
    window_index.title("体能成绩信息管理系统")
    Label(window_index, text='欢 迎 使 用 体 能 成 绩 信 息 管 理 系 统',
          font=tkFont.Font(family=' Microsoft YaHei', size=20, weight=tkFont.BOLD),
          width=80, height=2, bg='#0463b4', fg='white').grid(row=0, sticky=W + E)

    columns = ('姓名', '学号', '3000米', '30*2蛇形跑', '引体向上', '仰卧起坐', '总分', '总评')
    table = Treeview(window_index, height=14, show="headings", columns=columns)
    table.column('姓名', width=150, anchor='center');
    table.column('学号', width=150, anchor='center')
    table.column('3000米', width=150, anchor='center');
    table.column('30*2蛇形跑', width=150, anchor='center')
    table.column('引体向上', width=150, anchor='center');
    table.column('仰卧起坐', width=150, anchor='center')
    table.column('总分', width=150, anchor='center');
    table.column('总评', width=150, anchor='center')
    table.heading('姓名', text="姓名");
    table.heading('学号', text="学号")
    table.heading('3000米', text="3000米");
    table.heading('30*2蛇形跑', text="30*2蛇形跑")
    table.heading('引体向上', text="引体向上");
    table.heading('仰卧起坐', text="仰卧起坐")
    table.heading('总分', text="总分");
    table.heading('总评', text="总评")
    all_data(name, usr, psw)
    table.grid(row=1, sticky=W + E)
    frame = Frame(window_index)
    frame.grid(row=2, pady=20)
    names = StringVar()
    ids = StringVar()
    grade_l = StringVar();
    grade_m = StringVar()
    grade_e = StringVar();
    grade_p = StringVar()
    grade_c = StringVar();
    grade_b = StringVar()
    to_table = StringVar()
    v = StringVar()
    v.set('降序')
    Label(frame, text="姓名：").grid(row=0, column=0)
    Entry(frame, textvariable=names, highlightcolor='blue', highlightthickness=1).grid(row=0, column=1, padx=10)
    Label(frame, text="学号：").grid(row=1, column=0)
    Entry(frame, textvariable=ids, highlightcolor='blue', highlightthickness=1).grid(row=1, column=1, padx=10)
    Label(frame, text="3000米：").grid(row=2, column=0)
    Entry(frame, textvariable=grade_l, highlightcolor='blue', highlightthickness=1).grid(row=2, column=1, padx=10)
    Label(frame, text="30*2蛇形跑：").grid(row=3, column=0)
    Entry(frame, textvariable=grade_m, highlightcolor='blue', highlightthickness=1).grid(row=3, column=1, padx=10)
    Label(frame, text="引体向上：").grid(row=0, column=2)
    Entry(frame, textvariable=grade_e, highlightcolor='blue', highlightthickness=1).grid(row=0, column=3, padx=10)
    Label(frame, text="仰卧起坐：").grid(row=1, column=2)
    Entry(frame, textvariable=grade_p, highlightcolor='blue', highlightthickness=1).grid(row=1, column=3, padx=10)
    Label(frame, text="总分：").grid(row=2, column=2)
    Entry(frame, textvariable=grade_c, highlightcolor='blue', highlightthickness=1).grid(row=2, column=3, padx=10)
    Label(frame, text="总评：").grid(row=3, column=2)
    Entry(frame, textvariable=grade_b, highlightcolor='blue', highlightthickness=1).grid(row=3, column=3, padx=10)
    Label(frame, text="表格跳转：").grid(row=2, column=4, sticky=W + E)
    Entry_table_jump = Entry(frame, textvariable=to_table, highlightcolor='blue', highlightthickness=1)
    Entry_table_jump.grid(row=2, column=5, columnspan=2, sticky=W + E)
    Entry_table_jump.bind("<Return>", table_jump_fun)  # 可以按 enter 直接跳转，代替鼠标点击
    ck = Checkbutton(frame, variable=v, text='降序', font='宋体 -18', onvalue='降序', offvalue='升序', command=order_event)
    ck.grid(row=3, column=4, padx=50, sticky=W)

    Button(frame, text="查询成绩", width=12, command=lambda: look(name, usr, psw)).grid(row=0, column=4, padx=10, pady=2)
    Button(frame, text="成绩修改", width=12, command=lambda: alter(name, usr, psw)).grid(row=0, column=5, padx=10, pady=2)
    Button(frame, text="插入新项", width=12, command=lambda: insert(name, usr, psw)).grid(row=0, column=6, padx=10, pady=2)
    Button(frame, text="单科删除", width=12, command=lambda: delete(name, usr, psw)).grid(row=1, column=6, padx=10, pady=2)
    Button(frame, text="批量删除", width=12, command=lambda: muti_delete(name, usr, psw)).grid(row=1, column=5, padx=10,
                                                                                           pady=2)
    Button(frame, text="跳转至", width=12, command=table_jump).grid(row=1, column=4, padx=10, pady=2)
    window_index.mainloop()


if __name__ == '__main__':
    window = Tk()